package com.zfzs.post.modules.goods.repository;

import com.zfzs.post.modules.goods.domain.GoodOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 商品采购订单
 *
 * @author ck
 * @date 2019-10-18
 */
public interface GoodOrderRepository extends JpaRepository<GoodOrder, Integer>, JpaSpecificationExecutor {
    //根据时间、代理商查询数量
    @Query(value = "SELECT count(order_id) FROM good_order WHERE channel_id = ?1 AND agent_id IN (?2) AND create_date >= ?3 AND create_date <= ?4", nativeQuery = true)
    int queryCountByAgentIds(Long channelId, List<Long> ids, String startDate, String endDate);

    //采购新增订单列表
    @Query(value = "SELECT order_no as orderNo ,DATE_FORMAT(create_date,'%Y-%m-%d') AS createDate,goods_num as goodsNum,(select model from post_terminal_type where id = terminal_type_id )as model FROM good_order WHERE channel_id = ?1 AND agent_id IN (?2) AND create_date >= ?3 AND create_date <= ?4 ORDER BY create_time LIMIT ?5,?6", nativeQuery = true)
    List<Map<String,Object>> queryOrderByAgentIdsList(Long channelId, List<Long> ids, String startDate, String endDate,long pageNumber,int pageSize);
    //根据订单号查询
    GoodOrder findByOrderNo(String orderNo);

    @Query(value = "SELECT COUNT(order_no) FROM good_order WHERE channel_id = ?1 AND agent_id IN (?2) AND create_date >= ?3 AND create_date <= ?4 ORDER BY create_time", nativeQuery = true)
    int queryOrderByAgentIdsCount(Long channelId, List<Long> ids, String startDate, String endDate);

    @Query(value = "SELECT IFNULL(SUM(goods_num),0) FROM good_order WHERE channel_id = ?1 AND agent_id IN (?2) AND create_date >= ?3 AND create_date <= ?4 ORDER BY create_time", nativeQuery = true)
    int queryOrderByAgentIdsSum(Long channelId, List<Long> ids, String startDate, String endDate);
}